﻿Imports System.Data
Imports System.Data.SqlClient

Imports BE = BusinessEntities

Public Class RendicionesDAO

    'Declaración de variables
    Dim conexion As SqlConnection
    Dim da As SqlDataAdapter
    Dim str_error As String
    Dim bool_resultado As Boolean
    Dim cmd As SqlCommand

    'Instanciamos la clase Conexion.vb
    Dim cn As New Conexion



    Public Function ListarRequisiciones(ByVal objeto As BE.UsuarioDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_LISTARREQUISICIONES", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@COD_USU", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_USU").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_USU").Value = objeto.CodigoUsuario

            da.Fill(ds, "DATA_REQUISICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function
    Public Function recuperarMaximoRendimiento() As Int32
        conexion = cn.Conectar

        Dim comando As New SqlCommand("SP_MAX_COD_RENDICION", conexion)
        comando.CommandType = CommandType.StoredProcedure

        comando.Parameters.Add(New SqlParameter("@COD_REN", SqlDbType.Int))
        comando.Parameters("@COD_REN").Direction = ParameterDirection.Output

        conexion.Open()
        comando.ExecuteNonQuery()
        conexion.Close()

        Dim numeromov As String
        numeromov = comando.Parameters(0).Value
        Return numeromov
        comando.Dispose()
        conexion.Dispose()
    End Function
    Public Function ListarTiposDocumentos() As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_TIPOS_DOCUMENTOS", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.Fill(ds, "DATA_TIPO_DOCUMENTOS")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function



    Public Function InsertarRendicion(ByVal objeto As BE.RendicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_INSERT_RENDICIONES", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@COD_USU", SqlDbType.Int))
            cmd.Parameters("@COD_USU").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_USU").Value = objeto.Codigo_Usuario

            cmd.Parameters.Add(New SqlParameter("@COD_REQ", SqlDbType.Int))
            cmd.Parameters("@COD_REQ").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_REQ").Value = objeto.Codigo_Requisicion

            cmd.Parameters.Add(New SqlParameter("@COD_EST_DOC", SqlDbType.Int))
            cmd.Parameters("@COD_EST_DOC").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_EST_DOC").Value = objeto.Codigo_Estado_Documento

            cmd.Parameters.Add(New SqlParameter("@FEC_GEN_REN", SqlDbType.Date))
            cmd.Parameters("@FEC_GEN_REN").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_GEN_REN").Value = objeto.Fecha_Generado_Rendicion

            cmd.Parameters.Add(New SqlParameter("@GAS_SUB_TOT", SqlDbType.Decimal))
            cmd.Parameters("@GAS_SUB_TOT").Direction = ParameterDirection.Input
            cmd.Parameters("@GAS_SUB_TOT").Value = objeto.Gasto_Sub_Total

            cmd.Parameters.Add(New SqlParameter("@DEV_IMP_AVT", SqlDbType.Decimal))
            cmd.Parameters("@DEV_IMP_AVT").Direction = ParameterDirection.Input
            cmd.Parameters("@DEV_IMP_AVT").Value = objeto.Devolucion_Importe_Avantec

            cmd.Parameters.Add(New SqlParameter("@PAG_TEC", SqlDbType.Decimal))
            cmd.Parameters("@PAG_TEC").Direction = ParameterDirection.Input
            cmd.Parameters("@PAG_TEC").Value = objeto.Pago_Tecnico

            cmd.Parameters.Add(New SqlParameter("@FEC_FIR_ACTA", SqlDbType.Date))
            cmd.Parameters("@FEC_FIR_ACTA").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_FIR_ACTA").Value = objeto.Fecha_Firma_Acta

            cmd.Parameters.Add(New SqlParameter("@IMG_ARCHIVO", SqlDbType.VarChar, 200))
            cmd.Parameters("@IMG_ARCHIVO").Direction = ParameterDirection.Input
            cmd.Parameters("@IMG_ARCHIVO").Value = objeto.Imagen_Archivo

            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function
    Public Function actualizarArchivoRendicion(ByVal objeto As BE.RendicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("sp_subirImagen", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@COD_REN", SqlDbType.Int))
            cmd.Parameters("@COD_REN").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            cmd.Parameters.Add(New SqlParameter("@IMG_ARCHIVO", SqlDbType.VarChar, 200))
            cmd.Parameters("@IMG_ARCHIVO").Direction = ParameterDirection.Input
            cmd.Parameters("@IMG_ARCHIVO").Value = objeto.Imagen_Archivo

            
            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function


    Public Function listar_Resumen_Rendicion(ByVal objeto As BE.RendicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_RESUMEN_RENDICION", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@COD_REN", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_REN").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            da.Fill(ds, "DATA_RESUMEN_RENDICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function



    Public Function InsertarRendicion_detalles(ByVal objeto As BE.RendicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_INSERT_RENDICIONES_DETALLE", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@COD_REN", SqlDbType.Int))
            cmd.Parameters("@COD_REN").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            cmd.Parameters.Add(New SqlParameter("@COD_TIP_DOC", SqlDbType.Int))
            cmd.Parameters("@COD_TIP_DOC").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_TIP_DOC").Value = objeto.Codigo_Tipo_Documento

            cmd.Parameters.Add(New SqlParameter("@FEC_RBF", SqlDbType.Date))
            cmd.Parameters("@FEC_RBF").Direction = ParameterDirection.Input
            cmd.Parameters("@FEC_RBF").Value = objeto.Fecha_Recibo_Boleta_factura


            cmd.Parameters.Add(New SqlParameter("@COD_GAS", SqlDbType.Int))
            cmd.Parameters("@COD_GAS").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_GAS").Value = objeto.Codigo_Gasto

            cmd.Parameters.Add(New SqlParameter("@NRO_RBF", SqlDbType.VarChar, 15))
            cmd.Parameters("@NRO_RBF").Direction = ParameterDirection.Input
            cmd.Parameters("@NRO_RBF").Value = objeto.Numero_Recibo_Boleta_Factura

            cmd.Parameters.Add(New SqlParameter("@DES_RCB", SqlDbType.VarChar, 100))
            cmd.Parameters("@DES_RCB").Direction = ParameterDirection.Input
            cmd.Parameters("@DES_RCB").Value = objeto.Descripcion_RCB

            cmd.Parameters.Add(New SqlParameter("@IMP_GAS", SqlDbType.Decimal))
            cmd.Parameters("@IMP_GAS").Direction = ParameterDirection.Input
            cmd.Parameters("@IMP_GAS").Value = objeto.Importe_Gasto

            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function

    Public Function actualizarRendicion(ByVal objeto As BE.RendicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_ACTUALIZAR_RENDICION", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@COD_REN", SqlDbType.Int))
            cmd.Parameters("@COD_REN").Direction = ParameterDirection.Input
            cmd.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            cmd.Parameters.Add(New SqlParameter("@GAS_SUB_TOT", SqlDbType.Decimal))
            cmd.Parameters("@GAS_SUB_TOT").Direction = ParameterDirection.Input
            cmd.Parameters("@GAS_SUB_TOT").Value = objeto.Gasto_Sub_Total

            cmd.Parameters.Add(New SqlParameter("@DEV_EMP_AVT", SqlDbType.Decimal))
            cmd.Parameters("@DEV_EMP_AVT").Direction = ParameterDirection.Input
            cmd.Parameters("@DEV_EMP_AVT").Value = objeto.Devolucion_Importe_Avantec

            cmd.Parameters.Add(New SqlParameter("@PAG_TEC", SqlDbType.Decimal))
            cmd.Parameters("@PAG_TEC").Direction = ParameterDirection.Input
            cmd.Parameters("@PAG_TEC").Value = objeto.Pago_Tecnico

            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function


    Public Function eliminarRendicionDetalle(ByVal objeto As BE.RendicionDTO) As Boolean

        Dim cn As New Conexion
        Try
            conexion = cn.Conectar

            cmd = New SqlCommand("SP_ELIMINAR_RENDICION_DETALLE", conexion)

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add(New SqlParameter("@ID_REN", SqlDbType.Int))
            cmd.Parameters("@ID_REN").Direction = ParameterDirection.Input
            cmd.Parameters("@ID_REN").Value = objeto.Id_Rendicion


            conexion.Open()

            cmd.ExecuteNonQuery()
            bool_resultado = True

        Catch ex As Exception
            bool_resultado = False
            str_error = ex.Message
        Finally

            If conexion.State = ConnectionState.Open Then conexion.Close()
            conexion.Dispose()
            cmd.Dispose()
            cn = Nothing

        End Try

        Return bool_resultado

    End Function

    Public Function buscar_Rendicion(ByVal objeto As BE.UsuarioDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_BUSCAR_RENDICIONES", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add(New SqlParameter("@COD_USU", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_USU").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_USU").Value = objeto.CodigoUsuario

            da.Fill(ds, "DATA_BUSCAR_RENDICIONES")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function

    Public Function Listar_Buscar_Rendicion(ByVal objeto As BE.RendicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_BUSCAR_RENDICION", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@COD_REN", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_REN").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            da.Fill(ds, "DATA_BUSCA_REENDICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function
    Public Function Listar_Buscar_Rendicion_detalle(ByVal objeto As BE.RendicionDTO) As DataSet
        Dim ds As New DataSet
        Try
            conexion = cn.Conectar
            da = New SqlDataAdapter("SP_BUSCAR_RENDICION_DETALLE", conexion)
            da.SelectCommand.CommandType = CommandType.StoredProcedure

            da.SelectCommand.Parameters.Add _
            (New SqlParameter("@COD_REN", SqlDbType.Int))

            da.SelectCommand.Parameters("@COD_REN").Direction = ParameterDirection.Input
            da.SelectCommand.Parameters("@COD_REN").Value = objeto.Codigo_Rendicion

            da.Fill(ds, "DATA_BUSCA_REENDICION")

        Catch ex As Exception
            str_error = ex.Message
        Finally
            conexion.Dispose()
            ds.Dispose()
            da.Dispose()
        End Try

        Return ds

    End Function




    Public Function CapturaError() As String
        Return str_error
    End Function




End Class
